<?php

namespace app\admin\controller;

use app\admin\model\Admin;
use app\admin\model\User;
use app\common\controller\Backend;
use app\common\model\Attachment;
use fast\Date;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Db;

/**
 * 控制台
 *
 * @icon   fa fa-dashboard
 * @remark 用于展示当前系统中的统计数据、统计报表及重要实时数据
 */
class Dashboard extends Backend
{

    /**
     * 查看
     */
    public function index()
    {
        try {
            \think\Db::execute("SET @@sql_mode='';");
        } catch (\Exception $e) {

        }

        $starttime = Date::unixtime('day', -6);
        //var_dump($starttime);
        $endtime = Date::unixtime('day', 0, 'end');

        if(isset($_GET['workstartTime'])  && isset($_GET['workendTime'])  && $_GET['workstartTime'] != ""  && $_GET['workendTime'] != ""){
            $workstartTime = substr($_GET['workstartTime'],0,10);
            $workendTime = substr($_GET['workendTime'],0,10);
        }else{
            $workstartTime = $starttime;
            $workendTime = $endtime;
        }

        //新增工单总量
        $column = [];
        /*$joinlist = Db("user")->where('jointime', 'between time', [$starttime, $endtime])
            ->field('jointime, status, COUNT(*) AS nums, DATE_FORMAT(FROM_UNIXTIME(jointime), "%Y-%m-%d") AS join_date')
            ->group('join_date')
            ->select();
        for ($time = $starttime; $time <= $endtime;) {
            $column[] = date("Y-m-d", $time);
            $time += 86400;
        }
        $userlist = array_fill_keys($column, 0);
        foreach ($joinlist as $k => $v) {
            $userlist[$v['join_date']] = $v['nums'];
        }*/
        $gongdan_where['id'] = ['>',0];

        if(isset($_GET['product_id'])  && $_GET['product_id'] != 0){
            $gongdan_where['product_id'] = $_GET['product_id'];
        }

        $joinlist = Db("work")->where('createtime', 'between time', [$workstartTime, $workendTime])->where($gongdan_where)
            ->field('createtime, status, COUNT(*) AS nums, DATE_FORMAT(FROM_UNIXTIME(createtime), "%Y-%m-%d") AS join_date')
            ->group('join_date')
            ->select();

        for ($time = $workstartTime; $time <= $workendTime;) {
            $column[] = date("Y-m-d", $time);
            $time += 86400;
        }
        $userlist = array_fill_keys($column, 0);
        foreach ($joinlist as $k => $v) {
            $userlist[$v['join_date']] = $v['nums'];
        }
        //多次维修工单
        $twojoinlist = Db("work")->where('createtime', 'between time', [$workstartTime, $workendTime])->where('is_two',1)->where($gongdan_where)
            ->field('createtime, status, COUNT(*) AS nums, DATE_FORMAT(FROM_UNIXTIME(createtime), "%Y-%m-%d") AS join_date')
            ->group('join_date')
            ->select();
        for ($time = $workstartTime; $time <= $workendTime;) {
            $column[] = date("Y-m-d", $time);
            $time += 86400;
        }
        $twouserlist = array_fill_keys($column, 0);
        foreach ($twojoinlist as $k => $v) {
            $twouserlist[$v['join_date']] = $v['nums'];
        }

        $knowjoinlist = Db("know")->where('createtime', 'between time', [$starttime, $endtime])
            ->field('id,createtime, COUNT(*) AS nums, DATE_FORMAT(FROM_UNIXTIME(createtime), "%Y-%m-%d") AS join_date')
            ->group('join_date')
            ->select();
        for ($time = $starttime; $time <= $endtime;) {
            $knowcolumn[] = date("Y-m-d", $time);
            $time += 86400;
        }
        $knowlist = array_fill_keys($knowcolumn, 0);

        foreach ($knowcolumn as $k => $v) {
           // echo $v['join_date'] ."__";
            //这个时间之前注册的数
            $kn_t = strtotime($v)+86400;
            $kn_sum = Db::name("know")->where('createtime','<',$kn_t)->count();
            $knowlist[$v] = $kn_sum;

        }

        $knowlogjoinlist = Db("know_log")->where('createtime', 'between time', [$starttime, $endtime])
            ->field('id,createtime, COUNT(*) AS nums, DATE_FORMAT(FROM_UNIXTIME(createtime), "%Y-%m-%d") AS join_date')
            ->group('join_date')
            ->select();
        for ($time = $starttime; $time <= $endtime;) {
            $knowcolumn[] = date("Y-m-d", $time);
            $time += 86400;
        }
        $knowloglist = array_fill_keys($knowcolumn, 0);

        foreach ($knowlogjoinlist as $k => $v) {
            $knowloglist[$v['join_date']] = $v['nums'];

        }

        $dbTableList = Db::query("SHOW TABLE STATUS");
        $addonList = get_addon_list();
        $totalworkingaddon = 0;
        $totaladdon = count($addonList);
        foreach ($addonList as $index => $item) {
            if ($item['state']) {
                $totalworkingaddon += 1;
            }
        }
        $sdefaultDate = date("Y-m-d");
        //$first =1 表示每周星期一为开始日期 0表示每周日为开始日期
        $first=1;
        //获取当前周的第几天 周日是 0 周一到周六是 1 - 6
        $w=date('w',strtotime($sdefaultDate));
        //获取本周开始日期，如果$w是0，则表示周日，减去 6 天
        $week_start=strtotime("$sdefaultDate -".($w ? $w - $first : 6).' days');
        //今日审核平均时长
        $shenhe_jin_t = 0;
        $shenhe_jin_time = 0;
        $today_shenhe = Db::name("work")->whereTime('auditing_time','today')->select();//今日审核
        if($today_shenhe){
            $shenhe_sun_time = 0;//审核总时长
            foreach ($today_shenhe as $k=>$v){
                $shenhe_sun_time += $v['auditing_time'] - $v['createtime'];
            }
            $shenhe_jin_time = $shenhe_sun_time/count($today_shenhe);
            $shenhe_jin_t = self::secondsToTime($shenhe_jin_time);//今日审核平均时长
        }
        //
        $yesshenhe_jin_t = 0;
        $yesshenhe_jin_time = 0;
        // 获取昨日的开始和结束时间戳
        $yesterdayStart = strtotime('yesterday 00:00:00');
        $yesterdayEnd = strtotime('yesterday 23:59:59');
        $yesterdayWork = Db::name('work')
            ->whereTime('auditing_time', '>=', $yesterdayStart)
            ->whereTime('auditing_time', '<=', $yesterdayEnd)
            ->select();
        if($yesterdayWork){
            $yesshenhe_sun_time = 0;//审核总时长
            foreach ($yesterdayWork as $k=>$v){
                $yesshenhe_sun_time += $v['auditing_time'] - $v['createtime'];
            }
            $yesshenhe_jin_time = $yesshenhe_sun_time/count($yesterdayWork);
           // $yesshenhe_jin_t = self::secondsToTime($yesshenhe_jin_time);//今日审核平均时长
        }
        //echo $yesshenhe_jin_time ."=". $shenhe_jin_time;

       $bili =  1-($shenhe_jin_time-$yesshenhe_jin_time)/100;//较昨日提升
        $bili = sprintf("%.2f", $bili);
        if($shenhe_jin_time == 0){
            $bili = 0.00;
        }
        //工厂处理平均时长 factorysend_time
        $gc_time_t = 0;
        $gc_z_time = 0;
        $f_gc_z_time = 0;
        $gc_list = Db::name("work")->whereTime('factorysend_time','neq',null)->select();
        $gcpxbl = [];

        if($gc_list){
            $gc_time = 0;
            foreach ($gc_list as $k=>$v){
                $gc_z_time += $v['factorysend_time'] - $v['confirm_time'];
                if($v['voucher_aud_time']){
                    $f_gc_z_time += $v['voucher_aud_time'] - $v['detection_time'];
                }else{
                    $f_gc_z_time = 0;
                }
               //
                //echo $v['factorysend_time']. "-" .$v['voucher_aud_time']."____";

            }
            $gc_time = $gc_z_time - $f_gc_z_time;
            $gc_time_ttime = intval($gc_time/count($gc_list));

            $gc_time_t = self::secondsToTime($gc_time_ttime);
        }
        if(isset($_GET['workstartTime']) && isset($_GET['workendTime'])  && $_GET['workstartTime'] != ""  && $_GET['workendTime'] != ""){
            $workstartTime = date('Y-m-d',substr($_GET['workstartTime'],0,10));
            $workendTime = date('Y-m-d',substr($_GET['workendTime'],0,10));
        }else{
            $workstartTime = '';
            $workendTime = '';
        }
        //var_dump($_GET['gcworkstartTime']);
        if(isset($_GET['gcworkstartTime']) && isset($_GET['gcworkendTime'])  && $_GET['gcworkstartTime'] != ""  && $_GET['gcworkendTime'] != ""){
            $gcworkstartTime = date('Y-m-d',substr($_GET['gcworkstartTime'],0,10));
            $gcworkendTime = date('Y-m-d',substr($_GET['gcworkendTime'],0,10));
        }else{
            $gcworkstartTime = '';
            $gcworkendTime = '';
        }
        if(isset($_GET['gctwoworkstartTime']) && isset($_GET['gctwoworkendTime'])  && $_GET['gctwoworkstartTime'] != ""  && $_GET['gctwoworkendTime'] != ""){
            $gctwoworkstartTime = date('Y-m-d',substr($_GET['gctwoworkstartTime'],0,10));
            $gctwoworkendTime = date('Y-m-d',substr($_GET['gctwoworkendTime'],0,10));
        }else{
            $gctwoworkstartTime = '';
            $gctwoworkendTime = '';
        }
        $gcxzList = ['0' => __('三天内'),'1' => __('七天内')];
        $gc_list = Db::name("work")->alias('w')
            ->join('factory f','f.id = w.factory_id','left')
            ->field("factory_id,title")
            ->where('w.factorysend_time','NOT NULL')
            ->group("w.factory_id")->select();

        //产品
        $product_list = Db::name("product")->select();
        $this->view->assign([
            'totaluser'         => User::count(),
            'totaladdon'        => $totaladdon,
            'totaladmin'        => Admin::count(),
            'totalcategory'     => \app\common\model\Category::count(),
            'todayusersignup'   => User::whereTime('jointime', 'today')->count(),
            'todayuserlogin'    => User::whereTime('logintime', 'today')->count(),
            'sevendau'          => User::whereTime('jointime|logintime|prevtime', '-7 days')->count(),
            'thirtydau'         => User::whereTime('jointime|logintime|prevtime', '-30 days')->count(),
            'threednu'          => User::whereTime('jointime', '-3 days')->count(),
            'sevendnu'          => User::whereTime('jointime', '-7 days')->count(),
            'dbtablenums'       => count($dbTableList),
            'dbsize'            => array_sum(array_map(function ($item) {
                return $item['Data_length'] + $item['Index_length'];
            }, $dbTableList)),
            'totalworkingaddon' => $totalworkingaddon,
            'attachmentnums'    => Attachment::count(),
            'attachmentsize'    => Attachment::sum('filesize'),
            'picturenums'       => Attachment::where('mimetype', 'like', 'image/%')->count(),
            'picturesize'       => Attachment::where('mimetype', 'like', 'image/%')->sum('filesize'),
            'worktodaysum'       => Db::name("work")->whereTime('createtime','today')->count(),//今日提交
            'worknotsum'       => Db::name("work")->where('status',"<>",10)->count(),//未完成订单
            'workmsum'       => Db::name("work")->where('createtime',">",$week_start)->where('status',10)->count(),//本周已完成
            'shenhe_jin_t'       => $shenhe_jin_t,//
            'bili'       => $bili,//
            'gc_time_t'       => $gc_time_t,//
            'workstartTime'       => $workstartTime?$workstartTime:'',//
            'workendTime'       => $workendTime?$workendTime:'',//
            'gcworkstartTime'       => $gcworkstartTime?$gcworkstartTime:'',//
            'gcworkendTime'       => $gcworkendTime?$gcworkendTime:'',//
            'gctwoworkstartTime'       => $gctwoworkstartTime?$gctwoworkstartTime:'',//
            'gctwoworkendTime'       => $gctwoworkendTime?$gctwoworkendTime:'',//
            'gcxzList'       => $gcxzList,//
            'gc_list'       => $gc_list,//
            'product_list'       => $product_list,//
            'product_id'       => isset($_GET['product_id'])?$_GET['product_id']:0,//
        ]);
        //工单占比
        $re_data = [];
        $re_data_arr = [];
        foreach ($gc_list as $k=>$v){
            $re_data[$k]['key'] = $k;
            $re_data[$k]['factory_name'] = Db::name("factory")->where(['id'=>$v['factory_id']])->value("title");
            $factory_work_data = Db::name("problem")->where(['pid'=>0])->select();
            $factory_work_sum_arr = [];
            $factory_work_name_arr = [];
            foreach ($factory_work_data as $k1=>$v1){
                $problem_ids = self::problem_ids($v1['id']);
                $problem_ids = trim($problem_ids,',');
                $problem_ids_arr = explode(',',$problem_ids);
                $factory_work_sum_cont = 0;
                foreach ($problem_ids_arr as $kk=>$vv){
                    $factory_work_sum = Db::name("work")->where("FIND_IN_SET(".$vv.",problem)")->where(['factory_id'=>$v['factory_id']])->count();
                    $factory_work_sum_cont += $factory_work_sum;
                }

                $factory_work_name =  Db::name("problem")->where(['id'=>$v1['id']])->where(['pid'=>0])->value("name");
                $factory_work_sum_arr[$k1]['name'] = $factory_work_name;
                $factory_work_sum_arr[$k1]['value'] = $factory_work_sum_cont;
                $factory_work_name_arr[$k1] = $factory_work_name;
                /*if($factory_work_sum == 0){
                   unset($factory_work_name_arr[$k1]);
                }
                if($factory_work_sum == 0){
                    unset($factory_work_sum_arr[$k1]);
                }*/
            }

            $re_data[$k]['factory_work_name_arr'] = array_values($factory_work_name_arr);
            $re_data[$k]['factory_work_sum_arr'] = array_values($factory_work_sum_arr);
        }


        if(isset($_GET['gcworkstartTime']) && isset($_GET['gcworkendTime']) && $_GET['gcworkstartTime'] != ""  && $_GET['gcworkendTime'] != ""){
            $gcworkstartTime = substr($_GET['gcworkstartTime'],0,10);
            $gcworkendTime = substr($_GET['gcworkendTime'],0,10);
            $factory_work_data = Db::name("work")->where('createtime', 'between time', [$gcworkstartTime, $gcworkendTime])->group("factory_id")->select();
            $factory_work_sum_arr = [];
            $factory_work_name_arr = [];
            foreach ($factory_work_data as $k=>$v){
                $factory_work_sum = Db::name("work")->where('createtime', 'between time', [$gcworkstartTime, $gcworkendTime])->where(['factory_id'=>$v['factory_id']])->count();
                $factory_work_name =  Db::name("factory")->where(['id'=>$v['factory_id']])->value("title");
                $factory_work_sum_arr[$k]['name'] = $factory_work_name;
                $factory_work_sum_arr[$k]['value'] = $factory_work_sum;
                $factory_work_name_arr[$k] = $factory_work_name;
            }
        }else{
            $factory_work_data = Db::name("work")->group("factory_id")->select();
            $factory_work_sum_arr = [];
            $factory_work_name_arr = [];
            foreach ($factory_work_data as $k=>$v){
                $factory_work_sum = Db::name("work")->where(['factory_id'=>$v['factory_id']])->count();
                $factory_work_name =  Db::name("factory")->where(['id'=>$v['factory_id']])->value("title");
                $factory_work_sum_arr[$k]['name'] = $factory_work_name;
                $factory_work_sum_arr[$k]['value'] = $factory_work_sum;
                $factory_work_name_arr[$k] = $factory_work_name;
            }
        }

        //二次工单占比

        if(isset($_GET['gctwoworkstartTime']) && isset($_GET['gctwoworkendTime']) && $_GET['gctwoworkstartTime'] != ""  && $_GET['gctwoworkendTime'] != ""){
            $gctwoworkstartTime = substr($_GET['gctwoworkstartTime'],0,10);
            $gctwoworkendTime = substr($_GET['gctwoworkendTime'],0,10);
            $factory_work_data_two = Db::name("work")->where('createtime', 'between time', [$gctwoworkstartTime, $gctwoworkendTime])->where(['is_two'=>1])->group("factory_id")->select();

            $factory_work_sum_arr_two = [];
            $factory_work_name_arr_two = [];
            foreach ($factory_work_data_two as $k=>$v){
                $factory_work_sum = Db::name("work")->where('createtime', 'between time', [$gctwoworkstartTime, $gctwoworkendTime])->where(['factory_id'=>$v['factory_id'],'is_two'=>1])->count();
                $factory_work_name =  Db::name("factory")->where(['id'=>$v['factory_id']])->value("title");
                $factory_work_sum_arr_two[$k]['name'] = $factory_work_name;
                $factory_work_sum_arr_two[$k]['value'] = $factory_work_sum;
                $factory_work_name_arr_two[$k] = $factory_work_name;
            }
        }else{
            $factory_work_data_two = Db::name("work")->where(['is_two'=>1])->group("factory_id")->select();

            $factory_work_sum_arr_two = [];
            $factory_work_name_arr_two = [];
            foreach ($factory_work_data_two as $k=>$v){
                $factory_work_sum = Db::name("work")->where(['factory_id'=>$v['factory_id'],'is_two'=>1])->count();
                $factory_work_name =  Db::name("factory")->where(['id'=>$v['factory_id']])->value("title");
                $factory_work_sum_arr_two[$k]['name'] = $factory_work_name;
                $factory_work_sum_arr_two[$k]['value'] = $factory_work_sum;
                $factory_work_name_arr_two[$k] = $factory_work_name;
            }
        }

//var_dump($factory_work_sum_arr_two);


        //var_dump($factory_work_data_two);
        //var_dump($userlist);
        //var_dump($knowloglist);
        $this->assignconfig('column', array_keys($userlist));
        $this->assignconfig('knowcolumn', array_keys($knowlist));
        $this->assignconfig('knowdata', array_values($knowlist));
        $this->assignconfig('knowlogdata', array_values($knowloglist));
        $this->assignconfig('userdata', array_values($userlist));
        $this->assignconfig('twouserdata', array_values($twouserlist));
        $this->assignconfig('factory_work_sum_arr', array_values($factory_work_sum_arr));
        $this->assignconfig('factory_work_sum_arr_two', array_values($factory_work_sum_arr_two));
        $this->assignconfig('factory_work_name_arr', array_values($factory_work_name_arr));
        $this->assignconfig('factory_work_name_arr_two', array_values($factory_work_name_arr_two));
        $this->assignconfig('factory_work_name_arr_thr', array_values($re_data));



        return $this->view->fetch();
    }
    public function problem_ids($pid){
        $ids_str = $pid.",";
        $list1 = Db::name("problem")->where(['pid'=>$pid])->select();
        if($list1){
            foreach ($list1 as $k=>$v){
                $ids_str .= $v['id'].",";
                $list2 = Db::name("problem")->where(['pid'=>$v['id']])->select();
                if($list2){
                    foreach ($list2 as $k1=>$v1){
                        $ids_str .= $v1['id'].",";
                    }
                }
            }
        }
        return $ids_str;
    }
    function secondsToTime($seconds) {
      $hours = floor($seconds / 3600);
      $minutes = floor(($seconds - ($hours * 3600)) / 60);
      $seconds = $seconds - ($hours * 3600) - ($minutes * 60);
      return str_pad($hours, 2, '0', STR_PAD_LEFT).':'.str_pad($minutes, 2, '0', STR_PAD_LEFT).':'.str_pad($seconds, 2, '0', STR_PAD_LEFT);
    }
   /* public function gcbl(){
        $type = $this->request->post('type');
        $gc_list = Db::name("work")->where('factorysend_time','NOT NULL')->group("factory_id")->select();
        //echo Db::name("work")->getLastSql();
        $gcpxbl = [];
        if($gc_list){

            foreach ($gc_list as $k=>$v){
                $factory_work = Db::name("work")->where('factorysend_time','NOT NULL')->where(['factory_id'=>$v['factory_id']])->select();
                $gc_z_time = 0;
                $f_gc_z_time = 0;

                foreach ($factory_work as $k1=>$v1){
                   // $gc_time += $v1['factorysend_time'] - $v1['voucher_aud_time'];

                    //工厂总时间
                    $gc_z_time += $v1['factorysend_time']-$v1['confirm_time'];
                    //非工厂处理时间
                    $f_gc_z_time += $v1['confirmdetection_time']-$v1['detection_time'];
                }
                $gc_time = $gc_z_time - $f_gc_z_time;
               /* $factory_name = Db::name("factory")->where(['id'=>$v['factory_id']])->value("title");

                $gcpxbl[$k]['factory_name'] = $factory_name;
                // $gcpxbl_t = self::secondsToTime($gc_time);
                //echo $gc_time ."___".count($factory_work);exit;
                $gc_time = intval(sprintf("%.2f",$gc_time/count($factory_work)));
                if($type == 1){
                    //if($v['gc_time'] <= 259200 || $v['gc_time'] > 604088){
                    if($gc_time <= 259200){//去掉小于三天的
                        unset($gcpxbl[$k]);
                        continue;
                    }
                }else{
                    if($gc_time > 259200){//去掉大于三天的
                        unset($gcpxbl[$k]);
                        continue;
                    }
                }*
                $gcpxbl[$k]['gc_time'] = $gc_time;
                //echo $v['factorysend_time']. "-" .$v['voucher_aud_time']."____";




            }
            usort($gcpxbl, function($a, $b) {
                return $a['gc_time'] <=> $b['gc_time'];
            });
            //var_dump($gc_list);
            $san = 0;
            $qi = 0;
            foreach ($gcpxbl as $k=>$v){
                $gcpxbl[$k]['key'] = $k+1;
                $v['gc_time'] = intval($v['gc_time']);
                    if($v['gc_time'] <= 259200 || $v['gc_time'] > 604088){
                        $qi += 1;
                    }
                    if($v['gc_time'] > 259200){//去掉大于三天的
                        $san += 1;
                    }
                //echo $v['gc_time']."__";
                $gcpxbl_t = self::secondsToTime($v['gc_time']);
                $gcpxbl[$k]['gcpxbl_t'] = $gcpxbl_t;
            }

            $re_gcpxbl['san'] = sprintf("%.2f",$san/count($factory_work)/100);
            $re_gcpxbl['qi'] = sprintf("%.2f",$qi/count($factory_work)/100);
            $re_data[] = $re_gcpxbl;
            return json($re_data);

        }
    }*/
    public function gcbl(){
        $gcpaihangstart_time = $this->request->post('gcpaihangstart_time');
        $gcpaihangend_time = $this->request->post('gcpaihangend_time');
        $where['id'] = ['>',0];
        if(isset($gcpaihangstart_time) && $gcpaihangstart_time != "" ) {
            $gcworkstartTime = substr($gcpaihangstart_time, 0, 10);
            $where['createtime'] = ['>=',$gcworkstartTime];

        }
        if( isset($gcpaihangend_time) && $gcpaihangend_time != ""){
            $gcworkendTime = substr($gcpaihangend_time, 0, 10);
            $where['createtime'] = ['<=',$gcworkendTime];
        }


        if(isset($gcpaihangstart_time) && isset($gcpaihangend_time) && $gcpaihangstart_time != ""  && $gcpaihangend_time != ""){
            $gcworkstartTime = substr($gcpaihangstart_time, 0, 10);
            $gcworkendTime = substr($gcpaihangend_time, 0, 10);
            $where['createtime'] =  ['between time', [$gcworkstartTime, $gcworkendTime]];
        }


        $gc_list = Db::name("work")->where($where)->group("factory_id")->where(['status'=>10])->select();
        //echo Db::name("work")->getLastSql();
        $gcpxbl = [];
        if($gc_list){

            foreach ($gc_list as $k=>$v){
                $factory_work = Db::name("work")->where($where)->where(['status'=>10])->where(['factory_id'=>$v['factory_id']])->select();
                $gc_z_time = 0;
                $f_gc_z_time = 0;
                $san = 0;
                $qi = 0;
                foreach ($factory_work as $k1=>$v1){
                    // $gc_time += $v1['factorysend_time'] - $v1['voucher_aud_time'];

                    //工厂总时间
                    $gc_z_time = $v1['factorysend_time']-$v1['confirm_time'];
                    //非工厂处理时间
                    if($v1['voucher_aud_time']){
                        $f_gc_z_time = $v1['voucher_aud_time']-$v1['detection_time'];
                    }else{
                        $f_gc_z_time = 0;
                    }
                   //
                    $gc_time = $gc_z_time - $f_gc_z_time;
                    if($gc_time <= 259200){

                        $san += 1;
                    }
                    if($gc_time > 259200 && $gc_time < 604088){
                        $qi += 1;
                    }
                }

                $factory_name = Db::name("factory")->where(['id'=>$v['factory_id']])->value("title");

                $gcpxbl[$k]['factory_name'] = $factory_name;
                // $gcpxbl_t = self::secondsToTime($gc_time);
                //echo $gc_time ."___".count($factory_work);exit;
                $qi_time = sprintf("%.2f",$qi/count($factory_work));

                $gcpxbl[$k]['qi'] = $qi_time*100;
                $san_time = sprintf("%.2f",$san/count($factory_work));

                $gcpxbl[$k]['san'] = $san_time*100;
                //echo $v['factorysend_time']. "-" .$v['voucher_aud_time']."____";




            }
            return json($gcpxbl);

        }
    }
    public function serviceexport()
    {
        set_time_limit(0);//设置时间限制


        //var_dump($where);
//exit;
        //导出的字段
        /*$field = [
           'res_address','channel->name',"real_pay_time","status","admin->nickname","reject","reject_time",'admin_shipper_name','admin_shipper_code','usersend_time',"auditing_time",'confirm_time','problem'.'cost','factory_status','cost_content', 'detection_time','sales_status','confirmdetection_time','voucher_time','voucher_re','voucher_aud_re_time','voucher_aud_time','user>nickname', 'user>mobile', 'confirm_time','image'
        ];*/
        $field = [
            'factory_name','san','qi'
        ];
        //连表数据需要显示的字段，user>mobile表示为：user表下的mobile字段
        $fieldComment = [
            'id' => 'Id',
            'factory_name' => '工厂名称',
            'san' => '三天内',
            'qi' => '七天内',
        ];
        $fields = $this->getField($field);

        $objPHPExcel = new Spreadsheet();
        $firstChar = 65;
        $tempChar = '';
        $tempCharNum = '';
        foreach ($fields as $key => &$field) {
            //当列超出 A-Z时，为：AA-AZ,BA-BZ，以此类推
            if ($firstChar > 90) {
                $firstChar = 65; //当超出 Z 时，重新跳到 A 开始

                if ($tempChar == '') {
                    $tempCharNum = 65;//当超出 Z 时，从 A 开始时需要加上 A-Z
                } else {
                    $tempCharNum += 1;
                }
                $tempChar = chr($tempCharNum);
            }
            $column = chr($firstChar);
            $firstChar++;

            $char = $tempChar . $column; //哪一列

            //处理连表的导出字段
            if (is_numeric($field)) {
                $field = [
                    'field' => $key,
                    'comment' => $fieldComment[$key],
                    'type' => 'varchar',
                    'chose' => [],
                ];
            }
            $field['char'] = $char;//记录哪一列，数据循环时可直接取用
            //设置表头
            $objPHPExcel->getActiveSheet()->setCellValue($char . '1', $field['comment']);
            //改变长度
            $objPHPExcel->getActiveSheet()->getColumnDimension($char)->setWidth(30);
        }
//            halt($fields);

        //查询数据
        $wheres = [
            // "transport.status" => ['in', '1,2']
        ];

        //var_dump($wheres);
        //var_dump($where);exit;
       // $work_list = Db::name("work")->where(['id'=>$ids])->find();
        $gcpaihangstart_time = $this->request->post('startTime');
        $gcpaihangend_time = $this->request->post('endTime');
       // var_dump($gcpaihangstart_time);exit;
        $where['id'] = ['>',0];
        if(isset($gcpaihangstart_time) && $gcpaihangstart_time != "" ) {
            $gcworkstartTime = strtotime($gcpaihangstart_time);
            $where['createtime'] = ['>=',$gcworkstartTime];

        }
        if( isset($gcpaihangend_time) && $gcpaihangend_time != ""){
            $gcworkendTime = strtotime($gcpaihangend_time);
            $where['createtime'] = ['<=',$gcworkendTime];
        }


        if(isset($gcpaihangstart_time) && isset($gcpaihangend_time) && $gcpaihangstart_time != ""  && $gcpaihangend_time != ""){
            $gcworkstartTime = strtotime($gcpaihangstart_time);
            $gcworkendTime = strtotime($gcpaihangend_time);
            $where['createtime'] =  ['between time', [$gcworkstartTime, $gcworkendTime]];
        }


        $list = Db::name("work")->where('status',10)->where($where)->group("factory_id")->select();
        //echo Db::name("work")->getLastSql();
        $gcpxbl = [];
        if($list) {

            foreach ($list as $key => &$val) {
                $factory_work = Db::name("work")->where('status',10)->where($where)->where(['factory_id' => $val['factory_id']])->select();
                $gc_z_time = 0;
                $f_gc_z_time = 0;
                $san = 0;
                $qi = 0;
                foreach ($factory_work as $k1 => $v1) {
                    // $gc_time += $v1['factorysend_time'] - $v1['voucher_aud_time'];

                    //工厂总时间
                    $gc_z_time = $v1['factorysend_time'] - $v1['confirm_time'];
                    //非工厂处理时间
                    if($v1['voucher_aud_time']){
                        $f_gc_z_time = $v1['voucher_aud_time'] - $v1['detection_time'];
                    }else{
                        $f_gc_z_time = 0;
                    }

                    $gc_time = $gc_z_time - $f_gc_z_time;
                    if($gc_time <= 259200){

                        $san += 1;
                    }
                    if($gc_time > 259200 && $gc_time < 604088){//去掉大于三天的
                        $qi += 1;
                    }
                }

                $factory_name = Db::name("factory")->where(['id' => $val['factory_id']])->value("title");

                $list[$key]['factory_name'] = $factory_name;
                // $gcpxbl_t = self::secondsToTime($gc_time);
                //echo $gc_time ."___".count($factory_work);exit;
                $qi_time = sprintf("%.2f", $qi / count($factory_work));

                $list[$key]['qi'] = $qi_time*100;
               // $list[$key]['qi'] = strval($list[$key]['qi'])."%";
                $san_time = sprintf("%.2f", $san / count($factory_work));

                $list[$key]['san'] = $san_time*100;
                //$list[$key]['san'] =$list[$key]['qi']."%";
                //echo $v['factorysend_time']. "-" .$v['voucher_aud_time']."____";

                //var_dump(is_array($val));
                //$val = $val->toArray();
                $i = $key + 2;//表格是从2开始的,第1行为表头
                foreach ($fields as &$field) {
                    $data = $val[$field['field']] ?? '';
                    //连表显示的字段
                    if (strpos($field['field'], '>') !== false) {
                        $fieldArr = explode('>', $field['field']);
                        $data = $val[$fieldArr[0]][$fieldArr[1]];
                    }
                    //转换为时间格式
                    if (is_numeric($data) && mb_strlen($data) == 10) {
                        $data = date('Y-m-d H:i:s', $data);
                    }
                    //设置第行高度
                    $objPHPExcel->getActiveSheet()->getRowDimension($key)->setRowHeight(65);
                    //将选择项转换成对应的值
                    if (!empty($field['chose'])) {
                        $data = $field['chose'][$data];
                    }
                    //将char和varchar数据类型的单元格设置为文本类型，以防出现科学计数法
                    if (strpos($field['type'], 'char') !== false) {
                        $objPHPExcel->getActiveSheet()->setCellValueExplicit($field['char'] . $i, $data, 's');
                    } else {
                        $objPHPExcel->getActiveSheet()->setCellValue($field['char'] . $i, $data);
                    }

                }
            }
        }
        $outputFileName = date("YmdHis") . '导出.xls';

        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="' . $outputFileName . '"');
        header("Content-Transfer-Encoding: binary");
        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter = IOFactory::createWriter($objPHPExcel, 'Xlsx');
        $objWriter->save('php://output');
        exit;
    }
    /**
     * 获取导出表信息
     * @param $field array 需要显示的字段
     * @return int[]|string[]
     */
    public function getField($field = [])
    {
        $prefix = config('database')['prefix'];
        $table = "fa_work";//查询的表名（带前缀）
        $data = Db::query('SHOW FULL COLUMNS FROM ' . $table);

        $returnField = array_flip($field);  //键值对换
        //判断跳过注释为空的和需要忽略的字段
        foreach ($data as $val) {
            $add = [];
            //只获取导出的字段
            if (!in_array($val['Field'], $field)) continue;

            if ($val['Comment'] != '') {
                $add['field'] = $val['Field'];  //字段
                $add['comment'] = $val['Comment']; //注释
                $add['type'] = $val['Type']; //数据类型
                $add['chose'] = []; //选择项，当注释包含格式：类型:1=大,2=小，才有选择项
                //处理选择项
                if (strpos($val['Comment'], ':') !== false) {
                    $commentArr = explode(':', $val['Comment']);
                    $add['comment'] = $commentArr[0];
                    $commentArr = explode(',', $commentArr[1]);
                    foreach ($commentArr as $k => $v) {
                        $commentArr = explode('=', $v);
                        $add['chose'][$commentArr[0]] = $commentArr[1];
                    }
                }
                $returnField[$val['Field']] = $add;
            }
        }
        return $returnField;
    }
    /*
     public function gcbl(){
        $gc_list = Db::name("work")->where('factorysend_time','NOT NULL')->group("factory_id")->select();
        //echo Db::name("work")->getLastSql();
        $gcpxbl = [];
        if($gc_list){

            foreach ($gc_list as $k=>$v){
                $factory_work = Db::name("work")->where('factorysend_time','NOT NULL')->where(['factory_id'=>$v['factory_id']])->select();
                $gc_time = 0;
                foreach ($factory_work as $k1=>$v1){
                    $gc_time += $v1['factorysend_time'] - $v1['voucher_aud_time'];

                }
                $factory_name = Db::name("factory")->where(['id'=>$v['factory_id']])->value("title");

                $gcpxbl[$k]['factory_name'] = $factory_name;
                // $gcpxbl_t = self::secondsToTime($gc_time);
                //echo $gc_time ."___".count($factory_work);exit;
                $gcpxbl[$k]['gc_time'] = sprintf("%.2f",$gc_time/count($factory_work));
                //echo $v['factorysend_time']. "-" .$v['voucher_aud_time']."____";


            }
            usort($gcpxbl, function($a, $b) {
                return $a['gc_time'] <=> $b['gc_time'];
            });
            //var_dump($gc_list);
            foreach ($gcpxbl as $k=>$v){
                $gcpxbl[$k]['key'] = $k+1;
                $v['gc_time'] = intval($v['gc_time']);
                //echo $v['gc_time']."__";
                $gcpxbl_t = self::secondsToTime($v['gc_time']);
                $gcpxbl[$k]['gcpxbl_t'] = $gcpxbl_t;
            }
            return json($gcpxbl);

        }
    }*/
}
